/*
 Navicat Premium Dump SQL

 Source Server         : 127.0.0.1
 Source Server Type    : MySQL
 Source Server Version : 50744 (5.7.44)
 Source Host           : 127.0.0.1:3306
 Source Schema         : smart_community

 Target Server Type    : MySQL
 Target Server Version : 50744 (5.7.44)
 File Encoding         : 65001

 Date: 18/09/2025 15:54:46
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for sc_security_event
-- ----------------------------
DROP TABLE IF EXISTS `sc_security_event`;
CREATE TABLE `sc_security_event` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '事件ID',
  `event_type` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '事件类型：suspicious-可疑人员，damage-设施损坏，fire-消防隐患，other-其他',
  `severity` tinyint(1) NOT NULL DEFAULT '1' COMMENT '严重程度：1-一般，2-重要，3-紧急',
  `title` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '事件标题',
  `description` text COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '事件描述',
  `location` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '事件地点',
  `reporter_id` bigint(20) NOT NULL COMMENT '报告人ID',
  `handler_id` bigint(20) DEFAULT NULL COMMENT '处理人ID',
  `event_time` datetime NOT NULL COMMENT '事件发生时间',
  `handle_time` datetime DEFAULT NULL COMMENT '处理时间',
  `handle_result` text COLLATE utf8mb4_unicode_ci COMMENT '处理结果',
  `photos` text COLLATE utf8mb4_unicode_ci COMMENT '现场照片（JSON格式）',
  `status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '状态：1-待处理，2-处理中，3-已处理，4-已关闭',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `create_by` bigint(20) DEFAULT NULL COMMENT '创建人ID',
  `update_by` bigint(20) DEFAULT NULL COMMENT '更新人ID',
  `deleted` tinyint(1) NOT NULL DEFAULT '0' COMMENT '删除标志：0-未删除，1-已删除',
  `version` int(11) NOT NULL DEFAULT '0' COMMENT '版本号',
  PRIMARY KEY (`id`),
  KEY `idx_event_type` (`event_type`),
  KEY `idx_severity` (`severity`),
  KEY `idx_reporter_id` (`reporter_id`),
  KEY `idx_handler_id` (`handler_id`),
  KEY `idx_event_time` (`event_time`),
  KEY `idx_status` (`status`),
  KEY `idx_create_time` (`create_time`),
  CONSTRAINT `fk_security_event_handler` FOREIGN KEY (`handler_id`) REFERENCES `sc_user` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_security_event_reporter` FOREIGN KEY (`reporter_id`) REFERENCES `sc_user` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='安保事件表';

-- ----------------------------
-- Records of sc_security_event
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for sc_security_patrol
-- ----------------------------
DROP TABLE IF EXISTS `sc_security_patrol`;
CREATE TABLE `sc_security_patrol` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '巡逻记录ID',
  `route_id` bigint(20) NOT NULL COMMENT '路线ID',
  `user_id` bigint(20) NOT NULL COMMENT '巡逻用户ID',
  `patrol_date` date NOT NULL COMMENT '巡逻日期',
  `start_time` datetime NOT NULL COMMENT '开始时间',
  `end_time` datetime DEFAULT NULL COMMENT '结束时间',
  `checkpoints_completed` text COLLATE utf8mb4_unicode_ci COMMENT '已完成检查点（JSON格式）',
  `patrol_photos` text COLLATE utf8mb4_unicode_ci COMMENT '巡逻照片（JSON格式）',
  `abnormal_events` text COLLATE utf8mb4_unicode_ci COMMENT '异常事件记录',
  `status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '状态：1-进行中，2-已完成，3-异常中断',
  `remark` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '备注',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `create_by` bigint(20) DEFAULT NULL COMMENT '创建人ID',
  `update_by` bigint(20) DEFAULT NULL COMMENT '更新人ID',
  `deleted` tinyint(1) NOT NULL DEFAULT '0' COMMENT '删除标志：0-未删除，1-已删除',
  `version` int(11) NOT NULL DEFAULT '0' COMMENT '版本号',
  PRIMARY KEY (`id`),
  KEY `idx_route_id` (`route_id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_patrol_date` (`patrol_date`),
  KEY `idx_status` (`status`),
  KEY `idx_create_time` (`create_time`),
  CONSTRAINT `fk_security_patrol_route` FOREIGN KEY (`route_id`) REFERENCES `sc_security_route` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_security_patrol_user` FOREIGN KEY (`user_id`) REFERENCES `sc_user` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='安保巡逻记录表';

-- ----------------------------
-- Records of sc_security_patrol
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for sc_security_route
-- ----------------------------
DROP TABLE IF EXISTS `sc_security_route`;
CREATE TABLE `sc_security_route` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '路线ID',
  `route_name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '路线名称',
  `route_type` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '路线类型：patrol-巡逻，checkpoint-检查点',
  `description` text COLLATE utf8mb4_unicode_ci COMMENT '路线描述',
  `checkpoints` text COLLATE utf8mb4_unicode_ci COMMENT '检查点列表（JSON格式）',
  `frequency` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '巡逻频次：hourly-每小时，every_2_hours-每2小时，daily-每日',
  `duration_minutes` int(11) DEFAULT NULL COMMENT '预计用时（分钟）',
  `status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '状态：1-启用，2-停用',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `create_by` bigint(20) DEFAULT NULL COMMENT '创建人ID',
  `update_by` bigint(20) DEFAULT NULL COMMENT '更新人ID',
  `deleted` tinyint(1) NOT NULL DEFAULT '0' COMMENT '删除标志：0-未删除，1-已删除',
  `version` int(11) NOT NULL DEFAULT '0' COMMENT '版本号',
  `community_id` bigint(20) NOT NULL COMMENT '所属社区ID',
  PRIMARY KEY (`id`),
  KEY `idx_route_type` (`route_type`),
  KEY `idx_status` (`status`),
  KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='安保巡逻路线表';

-- ----------------------------
-- Records of sc_security_route
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for sc_security_route_user_rel
-- ----------------------------
DROP TABLE IF EXISTS `sc_security_route_user_rel`;
CREATE TABLE `sc_security_route_user_rel` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '关联ID',
  `route_id` bigint(20) NOT NULL COMMENT '路线ID',
  `user_id` bigint(20) NOT NULL COMMENT '用户ID',
  `is_primary` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否主要负责人：0-否，1-是',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `create_by` bigint(20) DEFAULT NULL COMMENT '创建人ID',
  `version` int(11) NOT NULL DEFAULT '0' COMMENT '版本号',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_route_user` (`route_id`,`user_id`),
  KEY `idx_route_id` (`route_id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_is_primary` (`is_primary`),
  KEY `idx_create_time` (`create_time`),
  CONSTRAINT `fk_security_route_user_route` FOREIGN KEY (`route_id`) REFERENCES `sc_security_route` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_security_route_user_user` FOREIGN KEY (`user_id`) REFERENCES `sc_user` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='安保路线负责人关联表';

-- ----------------------------
-- Records of sc_security_route_user_rel
-- ----------------------------
BEGIN;
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;
